package com.lt.test.provider.dao.impl;

import com.lt.test.common.util.StringUtils;
import com.lt.test.provider.dao.BaseDaoSupport;
import com.lt.test.provider.dao.QstiairesDao;
import org.springframework.stereotype.Repository;

import java.util.LinkedList;
import java.util.List;
import java.util.Map;

/**
 * @author lt
 * @date 2018/11/17 14:15
 */
@Repository
public class QstiairesDaoImpl extends BaseDaoSupport implements QstiairesDao {

    @Override
    public List<Map<String, Object>> getQstiaires(String custCode, String slsmanName, String channelName, String custCodeTop, String custName) {
        StringBuilder sqlStr = new StringBuilder();
        LinkedList<Object> params = new LinkedList<Object>();
        sqlStr.append("SELECT * FROM ( ");
        sqlStr.append("SELECT t2.CUST_NAME,T2.SLSMAN_NAME,T2.CUST_ORDER_LEVEL,T2.RTL_TYPE,t1.CUST_CODE,t1.TOPIC_ID,t1.ANSWER_NUMBER, t1.USER_NAME FU_STOCK FROM SYS_QSTIAIRES t1 FULL JOIN ( ");
        sqlStr.append("SELECT t.CUST_CODE,t.SLSMAN_NAME,t.CUST_NAME,t3.CUST_ORDER_LEVEL,t3.RTL_TYPE,t2.*FROM cgt_cust t FULL JOIN D_CUST_PROPERTY t3 ON t3.CUST_CODE=t.CUST_CODE,CHL_CHANNEL t1,chl_channel t2 WHERE t.sale_dept_code=t1.channel_code AND t1.parent_channel_id=t2.channel_id ");
        sqlStr.append(") t2 ON t1.CUST_CODE=t2.CUST_CODE WHERE t1.TYPE='11' ");

        if(!StringUtils.isNullOrEmpty(custCode)) {
            sqlStr.append("and t1.CUST_CODE = ? ");
            params.add(custCode);
        }
        if(!StringUtils.isNullOrEmpty(slsmanName)) {
            sqlStr.append("and t2.SLSMAN_NAME like ? ");
            params.add(slsmanName + '%');
        }
        if(!StringUtils.isNullOrEmpty(channelName)) {
            sqlStr.append("and t2.CHANNEL_NAME like ? ");
            params.add(channelName + '%');
        }
        if(!StringUtils.isNullOrEmpty(custCodeTop)) {
            sqlStr.append("and t1.CUST_CODE like ? ");
            params.add(custCodeTop + '%');
        }
        if(!StringUtils.isNullOrEmpty(custName)){
            sqlStr.append("and t2.CUST_NAME ?");
            params.add('%' + custName + '%');
        }
        sqlStr.append(") pivot (max(ANSWER_NUMBER) FOR TOPIC_ID IN ('84' Y1,'85' Y2,'86' Y3,'87' Y4,'88' Y5,'89' Y6,'90' Y7,'91' Y8,'92' Y9,'93' Y10,'94' Y11,'95' Y12,'96' Y13,'97' Y14,'98' Y15,'99' Y16,'100' Y17,'101' Y18,'102' Y19,'103' Y20,'104' Y21,'105' Y22,'106' Y23,'107' Y24,'108' Y25,'109' Y26,'110' Y27,'111' Y28))");
        return this.queryAsArray(sqlStr.toString(), params.toArray());
    }


    @Override
    public List<Map<String, Object>> getQstiairesStock() {
        StringBuilder sqlStr = new StringBuilder();
        LinkedList<Object> params = new LinkedList<Object>();
        sqlStr.append("SELECT A.TOPIC_ID,A.TOPIC_NAME,A.STOCK,A.ENQUIRY,B.ZS_STOCK,B.ZS_ENQUIRY,c.SC_STOCK,C.SC_ENQUIRY,D.PX_STOCK,D.PX_ENQUIRY,e.LZ_STOCK,e.LZ_ENQUIRY FROM ( ");
        sqlStr.append("SELECT t1.TOPIC_ID,t2.TOPIC_NAME,sum(instr(ANSWER_NUMBER,',')),sum(substr(ANSWER_NUMBER,1,instr(ANSWER_NUMBER,',')-1)) STOCK,sum(substr(ANSWER_NUMBER,instr(ANSWER_NUMBER,',')+1)) ENQUIRY FROM SYS_QSTIAIRES t1 FULL JOIN SYS_TOPIC t2 ON t2.TOPIC_ID=t1.TOPIC_ID FULL JOIN CGT_CUST t3 ON t1.cust_code=t3.cust_code FULL JOIN CHL_CHANNEL t4 ON t3.sale_dept_code=t4.channel_code WHERE t1.TYPE='11' ");
        sqlStr.append("GROUP BY t1.TOPIC_ID,t2.TOPIC_NAME ORDER BY t1.TOPIC_ID ） A FULL JOIN ( ");
        sqlStr.append("SELECT t1.TOPIC_ID,t2.TOPIC_NAME,sum(instr(ANSWER_NUMBER,',')),sum(substr(ANSWER_NUMBER,1,instr(ANSWER_NUMBER,',')-1)) ZS_STOCK,sum(substr(ANSWER_NUMBER,instr(ANSWER_NUMBER,',')+1)) ZS_ENQUIRY FROM SYS_QSTIAIRES t1 FULL JOIN SYS_TOPIC t2 ON t2.TOPIC_ID=t1.TOPIC_ID FULL JOIN CGT_CUST t3 ON t1.cust_code=t3.cust_code FULL JOIN CHL_CHANNEL t4 ON t3.sale_dept_code=t4.channel_code WHERE t1.TYPE='11' AND t4.CHANNEL_NAME LIKE '钟山%' GROUP BY t1.TOPIC_ID,t2.TOPIC_NAME) B ON A.TOPIC_ID=B.TOPIC_ID FULL JOIN ( ");
        sqlStr.append("SELECT t1.TOPIC_ID,t2.TOPIC_NAME,sum(instr(ANSWER_NUMBER,',')),sum(substr(ANSWER_NUMBER,1,instr(ANSWER_NUMBER,',')-1)) SC_STOCK,sum(substr(ANSWER_NUMBER,instr(ANSWER_NUMBER,',')+1)) SC_ENQUIRY FROM SYS_QSTIAIRES t1 FULL JOIN SYS_TOPIC t2 ON t2.TOPIC_ID=t1.TOPIC_ID FULL JOIN CGT_CUST t3 ON t1.cust_code=t3.cust_code FULL JOIN CHL_CHANNEL t4 ON t3.sale_dept_code=t4.channel_code WHERE t1.TYPE='11' AND t4.CHANNEL_NAME LIKE '水城%' GROUP BY t1.TOPIC_ID,t2.TOPIC_NAME) C ON c.TOPIC_ID=A.TOPIC_ID FULL JOIN ( ");
        sqlStr.append("SELECT t1.TOPIC_ID,t2.TOPIC_NAME,sum(instr(ANSWER_NUMBER,',')),sum(substr(ANSWER_NUMBER,1,instr(ANSWER_NUMBER,',')-1)) PX_STOCK,sum(substr(ANSWER_NUMBER,instr(ANSWER_NUMBER,',')+1)) PX_ENQUIRY FROM SYS_QSTIAIRES t1 FULL JOIN SYS_TOPIC t2 ON t2.TOPIC_ID=t1.TOPIC_ID FULL JOIN CGT_CUST t3 ON t1.cust_code=t3.cust_code FULL JOIN CHL_CHANNEL t4 ON t3.sale_dept_code=t4.channel_code WHERE t1.TYPE='11' AND t4.CHANNEL_NAME LIKE '盘县%' GROUP BY t1.TOPIC_ID,t2.TOPIC_NAME) d ON d.TOPIC_ID=a.TOPIC_ID FULL JOIN ( ");
        sqlStr.append("SELECT t1.TOPIC_ID,t2.TOPIC_NAME,sum(instr(ANSWER_NUMBER,',')),sum(substr(ANSWER_NUMBER,1,instr(ANSWER_NUMBER,',')-1)) LZ_STOCK,sum(substr(ANSWER_NUMBER,instr(ANSWER_NUMBER,',')+1)) LZ_ENQUIRY FROM SYS_QSTIAIRES t1 FULL JOIN SYS_TOPIC t2 ON t2.TOPIC_ID=t1.TOPIC_ID FULL JOIN CGT_CUST t3 ON t1.cust_code=t3.cust_code FULL JOIN CHL_CHANNEL t4 ON t3.sale_dept_code=t4.channel_code WHERE t1.TYPE='11' AND t4.CHANNEL_NAME LIKE '六枝%' GROUP BY t1.TOPIC_ID,t2.TOPIC_NAME) E ON e.TOPIC_ID=a.TOPIC_ID ");

        return this.queryAsArray(sqlStr.toString(), params.toArray());
    }
}
